﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using HNQXYJ.Model;


namespace HNQXYJ.DAL
{
    public   class UsersDAL
    {
        /// <summary>
        /// 通过存储过程得到数据表
        /// </summary>
        /// <param name="procName"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public static DataTable GetDataTable(string procName, params SqlParameter[] paras)
        {
            DataTable dt = SqlHelper.GetDataTable(procName, paras);
            return dt;
        }

        /// <summary>
        /// 通过存储过程执行所有的非查询语句
        /// </summary>
        /// <param name="procName">存储过程名称</param>
        /// <param name="paras">参数</param>
        /// <returns>返回受影响的行数</returns>
        public static int ExecNonQuery(string procName, params SqlParameter[] paras)
        {
            int res = SqlHelper.ExecNonQuery(procName, paras);
            return res;

        }


        /// <summary>
        /// 添加新用户
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        public int AddNewUser(Users user)
        {
            string procName = "UsersAdd";
            SqlParameter[] paras ={
                                 new SqlParameter("@UserName",SqlDbType.NVarChar,50),
                                 new SqlParameter("@Password",SqlDbType.NVarChar,50),
                                 new SqlParameter("@TrueName",SqlDbType.NVarChar,50),
                                 new SqlParameter("@RankName",SqlDbType.NVarChar,50),
                                 new SqlParameter("@RankLevel",SqlDbType.VarChar,100),
                                 new SqlParameter("@TouchMethod",SqlDbType.NVarChar,100),
                                 new SqlParameter("@AddDate",SqlDbType.DateTime),
                                 new SqlParameter("@ErrTimes",SqlDbType.Int),
                                 new SqlParameter("@Notes",SqlDbType.NVarChar,500)
                                 
                                 };
            paras[0].Value = user.UserName;
            paras[1].Value = user.Password;
            paras[2].Value = user.TrueName;
            paras[3].Value = user.RankName;
            paras[4].Value = user.RankLevel;
            paras[5].Value = user.TouchMethod;
            paras[6].Value = user.AddDate;
            paras[7].Value = user.ErrTimes;
            paras[8].Value = user.Notes;
            int res = ExecNonQuery(procName,paras);
            return res;
 
        }

        /// <summary>
        /// 更新用户信息
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        public bool UPdateUserById(Users user)
        {
            string procName = "UsersUpdateById";
            SqlParameter[] paras ={
                                 new SqlParameter("@Id",SqlDbType.Int),
                                 new SqlParameter("@UserName",SqlDbType.NVarChar,50),
                                // new SqlParameter("@Password",SqlDbType.NVarChar,50),
                                 new SqlParameter("@TrueName",SqlDbType.NVarChar,50),
                                 new SqlParameter("@RankName",SqlDbType.NVarChar,50),
                                 new SqlParameter("@RankLevel",SqlDbType.VarChar,100),
                                 new SqlParameter("@TouchMethod",SqlDbType.NVarChar,100),
                                 new SqlParameter("@Notes",SqlDbType.NVarChar,500)
                                  };
            paras[0].Value = user.ID;
            paras[1].Value = user.UserName;
          //  paras[2].Value = user.Password;
            paras[2].Value = user.TrueName;
            paras[3].Value = user.RankName;
            paras[4].Value = user.RankLevel;
            paras[5].Value = user.TouchMethod;        
            paras[6].Value = user.Notes;

            int res = ExecNonQuery(procName,paras);
            return res > 0;
        }

        /// <summary>
        /// 根据id删除用户
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool DeleteUserById(int id)
        {
            string procName = "UsersDeleteById";
            SqlParameter[] paras ={
                                 new SqlParameter("@Id",SqlDbType.Int)
                                 };
            paras[0].Value = id;

            int res = ExecNonQuery(procName, paras);
            return res>0;
        }

        /// <summary>
        /// 根据读出的一行数据将其转化为模型
        /// </summary>
        /// <param name="dr">读出的数据行</param>
        /// <returns>user</returns>
        private static Users ToModel(DataRow dr)
        {
            Users user = new Users();
            user.ID = Convert.ToInt32(dr["Id"]);
            user.UserName = dr["UserName"].ToString();
            user.Password = dr["Password"].ToString();
            user.TrueName = dr["TrueName"].ToString();
            user.RankName = dr["RankName"].ToString();
            user.RankLevel = dr["RanKLevel"].ToString();
            user.TouchMethod = dr["TouchMethod"].ToString();
            user.AddDate = Convert.ToDateTime(dr["AddDate"]);
            user.ErrTimes = Convert.ToInt32(dr["ErrTimes"]);
            user.Notes = dr["Notes"].ToString();
            return user;
        }

        /// <summary>
        /// 根据用户的id获得用户实例，中间将行转化为用户模型
        /// </summary>
        /// <param name="id">用户id</param>
        /// <returns>user 用户模型</returns>
        public Users GetUserDataById(int id)
        {
            string procName = "UsersGetDataById";
            SqlParameter[] paras = { 
                                   new SqlParameter("@Id",SqlDbType.Int)
                                   };
            paras[0].Value = id;
            DataTable dt = GetDataTable(procName, paras);
            if (dt.Rows.Count != 1)
            {
                return null;
            }
            else
            {
                DataRow dr = dt.Rows[0];
                Users user = ToModel(dr);
                return user;
            }       
        }

        /// <summary>
        /// 根据用户名获得用户数据
        /// </summary>
        /// <param name="username"></param>
        /// <returns></returns>
        public Users GetUserByUserName(string username)
        {
            string procName = "UsersGetDataByUserName";
            SqlParameter[] paras = { 
                                   new SqlParameter("@UserName",SqlDbType.NVarChar,50)
                                   };
            paras[0].Value = username;
            DataTable dt = GetDataTable(procName,paras);
            if (dt.Rows.Count == 1)
            {
                DataRow dr = dt.Rows[0];
                Users user = ToModel(dr);
                return user;
            }
            else
            {
                return null;
            }      
        }

        /// <summary>
        /// 根据用户的id来更新用户的密码
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool UpdateUserPassword( Users user)
        {
            bool flage = false;
            string procName = "UsersUpdatePassword";
            SqlParameter[] paras = { 
                                   new SqlParameter("@Id",SqlDbType.Int),
                                   new SqlParameter("@Password",SqlDbType.NVarChar,50)
                                   };
            paras[0].Value=user.ID;
            paras[1].Value=user.Password;
            
            int res=ExecNonQuery(procName,paras);
            if(res>0)
            {
                flage=true;
            }
            return flage;
        }

        /// <summary>
        /// 返回所有用户的列表
        /// </summary>
        /// <returns></returns>
        public List<Users> GetUsersListAll()
        {
            string procName = "UserGetDataAll";
            DataTable dt = GetDataTable(procName);
            List<Users> list=new List<Users>();
            foreach (DataRow row in dt.Rows)
            {
                list.Add(ToModel(row));
            }
            return list;
        }

        /// <summary>
        /// 根据用户权限获取对应的用户列表
        /// </summary>
        /// <param name="ranklevel"></param>
        /// <returns></returns>
        public List<Users> GetUserListByRankLevel(string ranklevel)
        {
            string procName = "UsersGetDataListByRankLevel";
            SqlParameter[] paras = { 
                                   new SqlParameter("@RankLevel",SqlDbType.VarChar,100)
                                   };
            paras[0].Value = ranklevel;
            DataTable dt = SqlHelper.GetDataTable(procName, paras);
            if (dt.Rows.Count > 0)
            {
                List<Users> list = new List<Users>();
                foreach (DataRow row in dt.Rows)
                {
                    list.Add(ToModel(row));
                }
                return list;
            }
            else
            {
                return null;
            }
        }
    }
}